using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class EmailDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(EmailID) from Email";
        string SQL_SELECT_ALL = "SELECT Email.EmailID, Email.Email FROM Contact INNER JOIN Email ON Contact.ContactID = Email.ContactID WHERE (((Email.ContactID)=@contactID))";
        string SQL_INSERT = "insert into Email(ContactID, Email) values(@contactID,@email)";
        string SQL_UPDATE = "update Email set  Email=@email where EmailID=@emailID";
        string SQL_DELETE = "delete from Email where EmailID=@emailID";
        #endregion
        #region fields
        string message;
        #endregion
        #region methods
        /// <summary>
        /// Get all email 
        /// </summary>
        /// <returns></returns>
        public IList<Email> SelectByContact(int contactID)
        {
            IList<Email> list = new List<Email>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL,conn);
                command.Parameters.AddWithValue("@contactID",contactID);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Email email = new Email();
                        email.EmailID = dr.GetInt32(0);
                        email.EmailAddress = dr.GetString(1);
                        list.Add(email);
                    }
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return list;
        }
        public int Insert(Email email,Contact contact)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.Add("@contactID", OleDbType.Integer).Value = contact.ContactID;
                command.Parameters.Add("@Email", OleDbType.VarChar).Value = email.EmailAddress;
              
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    email.EmailID = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Update(Email email)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);                
                command.Parameters.Add("@Email", OleDbType.VarChar).Value = email.EmailAddress;
                command.Parameters.Add("@EmailID", OleDbType.Integer).Value = email.EmailID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();                   
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        public int Delete(Email email)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.AddWithValue("@emailID", OleDbType.Integer).Value = email.EmailID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        #endregion
    }
}
